/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package quanlysinhvien;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import javafx.application.Application;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.geometry.Insets;
import javafx.scene.Scene;
import javafx.scene.control.*;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.scene.layout.*;
import javafx.scene.text.Font;
import javafx.stage.Stage;

/**
 *
 * @author trung
 */
public class QuanLySinhVien extends Application {

    Stage pStage;
    ConnectODBC_DSN conDSN;
    //layout
    private BorderPane border;
    private ResultSet rs = null;
    //data table
//    private final ObservableList<SinhVien> data=FXCollections.observableArrayList();
    //Head element
    public TextField txtSearch;
    public Button btnSearch;

    //Menu 
    public Button M_1;
    public Button M_2;
    public TabPane tabPane;

    //scen 1
    public TableView Tb_1;
    public Button btnSua;
    public Button btnXoa;
    //scene 2
    public TextField txtIdSV;
    public TextField txtNameSV;
    public TextField txtDoB;
    public TextArea txtComment;
    public ToggleGroup tG;
    public ComboBox cbxCity;
    
    public ComboBox cbxNgay;
    public ComboBox cbxThang;
    public ComboBox cbxNam;

    public Button btnThem;
    public Button btnHuy;

    @Override
    public void start(Stage primaryStage) {
        pStage = primaryStage;
        //Layout
        border = new BorderPane();
        //Đầu
        border.setTop(head());
        //Thân trái
        border.setLeft(menu());
        //Thân giữa
        border.setCenter(scre_1());
        M_1.setOnAction(new javafx.event.EventHandler<ActionEvent>() {

            @Override
            public void handle(ActionEvent t) {
                border.setCenter(scre_1());
            }
        });

        M_2.setOnAction(new javafx.event.EventHandler<ActionEvent>() {

            @Override
            public void handle(ActionEvent t) {
                border.setCenter(scre_2("", "", true, "", "Tỉnh/ thành phố",false));
            }
        });
        Scene scene = new Scene(border, 745, 600);
//        File direct=new File("");
//       scene.getStylesheets().add("css/S_Style.css");

        pStage.setTitle("Chương trình quản lý sinh viên");
        pStage.setScene(scene);
        pStage.show();
    }
    private final Connection con = null;

    private HBox head() {
        //Đối tượng chứa
        HBox head = new HBox();
        head.setStyle("-fx-background-color:#0BBDE5");
        head.setPadding(new Insets(5, 20, 5, 5));
        //label
        Label lbl_Title = new Label("Tìm theo : ");
        lbl_Title.setMinHeight(30);
        lbl_Title.setFont(new Font("arial", 15));
        //search textfield
        txtSearch = new TextField();
        txtSearch.setId("txtSearch");
        txtSearch.setMinSize(200, 30);
        //button search
        btnSearch = new Button("Tìm");
        btnSearch.setId("btnSearch");
        btnSearch.setMinSize(80, 28);

        btnSearch.setOnAction(new javafx.event.EventHandler<ActionEvent>() {

            @Override
            public void handle(ActionEvent t) {
                VBox scre_s = new VBox();
                //String[] lst=txtDoB.getText().split("-");
                ObservableList<SinhVien> data = FXCollections.observableArrayList();
                Tb_1 = new TableView();
                createTb();
                try {
                    ConnectODBC_ConString conDSN4 = new ConnectODBC_ConString();
                    Connection con = conDSN4.getCon();
                    Statement insSV2 = con.createStatement();
                    String sql = "select idSV, nameSV, dateOfBirth, sex, com, C.nameCon from list_student as LS, Country as C where LS.idCon=C.idCon and nameSV like '%" + txtSearch.getText() + "%'";
                    rs = insSV2.executeQuery(sql);
                    while (rs.next()) {
                        data.add(new SinhVien(
                                rs.getString("idSV"),
                                rs.getString("nameSV"),
                                rs.getString("dateOfBirth"),
                                Integer.parseInt(rs.getString("sex")),
                                rs.getString("nameCon"),
                                rs.getString("com")
                        ));
                    }
                    Tb_1.setItems(data);
                    conDSN4.Close();
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
                scre_s.getChildren().addAll(Tb_1);
                border.setCenter(scre_s);
            }
        });
        //Add for HBox
        head.getChildren().addAll(lbl_Title, txtSearch, btnSearch);
        return head;
    }

    private VBox menu() {
        VBox menu = new VBox();
        menu.setStyle("-fx-background-color:#89C8FF");
        menu.setPadding(new Insets(5, 5, 5, 5));
        VBox menu1 = new VBox();
        M_1 = new Button("Danh sách sinh viên");
        menu1.getChildren().add(M_1);
        VBox menu2 = new VBox();
        M_2 = new Button("Sinh Viên");
        menu2.getChildren().add(M_2);
        menu1.setPadding(new Insets(10, 0, 0, 0));
        menu2.setPadding(new Insets(10, 0, 10, 0));
        menu.getChildren().addAll(menu1, menu2);

        return menu;
    }

    private void createTb() {
        Tb_1 = new TableView();
        //Tb_1.getSelectionModel().setCellSelectionEnabled(true);
        Tb_1.getSelectionModel().setSelectionMode(SelectionMode.MULTIPLE);
        TableColumn idSV = new TableColumn("Mã SV");
        idSV.setMinWidth(50);
        idSV.setCellValueFactory(new PropertyValueFactory<SinhVien, String>("idSV"));
        TableColumn nameSV = new TableColumn("Tên Sinh Viên");
        nameSV.setCellValueFactory(new PropertyValueFactory<SinhVien, String>("nameSV"));
        TableColumn doB = new TableColumn("Ngày sinh");
        doB.setCellValueFactory(new PropertyValueFactory<SinhVien, String>("doB"));
        TableColumn sex = new TableColumn("Giới tính");
        sex.setMinWidth(50);
        sex.setCellValueFactory(new PropertyValueFactory<SinhVien, String>("sex"));
        TableColumn city = new TableColumn("Thành phố");
        sex.setMinWidth(100);
        city.setCellValueFactory(new PropertyValueFactory<SinhVien, String>("nameCon"));
        TableColumn comment = new TableColumn("Ghi chú");
        comment.setCellValueFactory(new PropertyValueFactory<SinhVien, String>("comment"));
        Tb_1.getColumns().addAll(idSV, nameSV, doB, sex, city, comment);
    }

    private VBox scre_1() {
        VBox scre_1 = new VBox();
        HBox row11;
        createTb();
        Label lbl_Title1 = new Label("Danh sách sinh viên : ");

        try {
            ObservableList<SinhVien> data = FXCollections.observableArrayList();
            //ConnectODBC_DSN conDSN = new ConnectODBC_DSN();
            ConnectODBC_ConString conMySQL = new ConnectODBC_ConString();
            Connection con = conMySQL.getCon();
            Statement stmt = con.createStatement();

            //Statement st = conn.createStatement();
            String sql = "select idSV, nameSV, dateOfBirth, sex, C.nameCon, com from list_student as LS, Country as C where LS.idCon=C.idCon";
            //String sql="select * from List_student";
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                data.add(new SinhVien(
                        rs.getString("idSV"),
                        rs.getString("nameSV"),
                        rs.getString("dateOfBirth"),
                        Integer.parseInt(rs.getString("sex")),
                        rs.getString("nameCon"),
                        rs.getString("com")
                ));
            }
            Tb_1.setItems(data);

            conMySQL.Close();
        } catch (Exception e) {
//            System.out.println("Không kết nối được CSDL");
            e.printStackTrace();
        }

        row11 = new HBox();
        btnSua = new Button("Sửa");
        btnSua.setMinSize(80, 28);

        btnSua.setOnAction(new javafx.event.EventHandler<ActionEvent>() {

            @Override
            public void handle(ActionEvent t) {
                try {
                    SinhVien sv = (SinhVien) Tb_1.getSelectionModel().getSelectedItem();
                    border.setCenter(scre_2(sv.getNameSV(), 
                            sv.getDoB(), 
                            (sv.getSex() == "Nam" ? true : false), 
                            sv.getComment(), 
                            sv.getNameCon(),true));
//                    String sql = "INSERT INTO List_Student(nameSV,dayofBirth,sex,idCon,comment) VALUES";
                    Stage stage = new Stage();
                    stage.setTitle("Sửa thông tin");
                    Scene scene2 = new Scene(border, 745, 600);
                    stage.setScene(scene2);
                    stage.show();
//                    pStage.setScene(scene2);
//                    pStage.show();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        });
        btnXoa = new Button("Xóa");
        btnXoa.setMinSize(80, 28);
        btnXoa.setLayoutX(400);
        row11.setPadding(new Insets(0, 0, 5, 400));
        row11.getChildren().addAll(btnSua, btnXoa);
        btnXoa.setOnAction(new javafx.event.EventHandler<ActionEvent>() {

            @Override
            public void handle(ActionEvent t) {
                try {
                    ConnectODBC_ConString conDSN2 = new ConnectODBC_ConString();//tạo kết nối
                    Connection con = conDSN2.getCon();//lấy kết nối
                    Statement stmt2 = con.createStatement();//thuc hien cau lenh
                    ObservableList<SinhVien> SV = Tb_1.getSelectionModel().getSelectedItems();//lấy table row
                    int i = 0;
                    while (i < SV.size()) {
                        String sql = "DELETE FROM List_Student where idSV=" + SV.get(i).getIdSV();
//                        System.out.println(SV.get(i).getIdSV());
                        stmt2.executeUpdate(sql);//Chạy
                        i++;
                    }

                    conDSN2.Close();//Đóng k
                    border.setCenter(scre_1());
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        });

        scre_1.getChildren().addAll(lbl_Title1, Tb_1, row11);
        return scre_1;

    }

    private VBox scre_2(String nameSV, String doB, boolean sex, String comment, String nameCon, boolean type) {
        VBox scre_2 = new VBox();
        HBox row2, row3, row4, row5, row6, row7, row8, row10;

        row2 = new HBox();
        Label lblNameSV = new Label("Tên SV: ");
        lblNameSV.setMinWidth(100);
        txtNameSV = new TextField();
        txtNameSV.setMaxSize(200, 30);
        txtNameSV.setText(nameSV);
        row2.getChildren().addAll(lblNameSV, txtNameSV);
        row2.setPadding(new Insets(5, 0, 5, 0));

        row3 = new HBox();
        Label lblDoB = new Label("Ngày sinh: ");
        lblDoB.setMinWidth(100);
//        txtDoB = new TextField();
//        txtDoB.setMaxSize(200, 30);
//        txtDoB.setText(doB);
        cbxNgay=new ComboBox();
        cbxThang=new ComboBox();
        cbxNam=new ComboBox();
        cbxNgay.setMaxSize(50, 30);
        cbxThang.setMaxSize(50, 30);
        cbxNam.setMaxSize(50, 30);
        row3.getChildren().addAll(lblDoB, cbxNgay,new Label("/"),cbxThang,new Label("/"),cbxNam);
        row3.setPadding(new Insets(5, 0, 5, 0));

        row4 = new HBox();
        Label lblSex = new Label("Giới tính: ");
        tG = new ToggleGroup();
        lblSex.setMinWidth(100);
        RadioButton Boy = new RadioButton("Nam");
        Boy.setToggleGroup(tG);
        Boy.setSelected(sex);
        Boy.setUserData(1);
        RadioButton Girl = new RadioButton("Nữ");
        Girl.setToggleGroup(tG);
        Girl.setUserData(0);
        row4.getChildren().addAll(lblSex, Boy, Girl);

        row8 = new HBox();
        Label lblComment = new Label("Ghi chú: ");
        lblComment.setMinWidth(100);
        txtComment = new TextArea();
        txtComment.setMaxSize(145, 100);
        txtComment.setText(comment);
        row8.getChildren().addAll(lblComment, txtComment);
        row8.setPadding(new Insets(5, 0, 5, 0));

        row6 = new HBox();
        ObservableList<String> data = FXCollections.observableArrayList();
        try {
            ConnectODBC_ConString conDSN = new ConnectODBC_ConString();
            rs = conDSN.GetResultSet("Country");
            while (rs.next()) {
                data.add(rs.getString("nameCon"));
            }
        } catch (Exception ex) {

        }
        Label lblCity = new Label("Thành phố: ");
        lblCity.setMinWidth(100);
        cbxCity = new ComboBox(data);
        cbxCity.setValue(nameCon);
        cbxCity.setMinWidth(120);
        row6.getChildren().addAll(lblCity, cbxCity);
        row6.setPadding(new Insets(5, 0, 5, 0));

        row10 = new HBox();
        btnHuy = new Button("Hủy");
        btnHuy.setMinSize(80, 28);
        if (!type) {
            btnThem = new Button("Thêm");
            btnThem.setMinSize(80, 28);
            btnThem.setLayoutX(400);
            btnThem.setOnAction(new javafx.event.EventHandler<ActionEvent>() {

                @Override
                public void handle(ActionEvent t) {
                    if (KiemTra() == true) {
                        try {
                            ConnectODBC_ConString conDSN3 = new ConnectODBC_ConString();
                            Connection con = conDSN3.getCon();
                            Statement insSV = con.createStatement();
                            Object val = tG.getSelectedToggle().getUserData();
//                    System.out.println("sadasd: "+val);
                            String sql = "INSERT INTO List_student(nameSV,dateOfBirth,sex,idCon,com) VALUES("
                                    + "'" + txtNameSV.getText() + "',"
                                    + "'" + txtDoB.getText() + "',"
                                    //+ "'"+tG.getToggles().get(selectedToggleIndex).isSelected(true)+"',"
                                    + val + ","
                                    + "" + (cbxCity.getSelectionModel().getSelectedIndex() + 1) + ","
                                    + "'" + txtComment.getText() + "'"
                                    + ")";
                            System.out.println(sql);
                            insSV.executeUpdate(sql);
                            System.out.println(rs);
                            conDSN3.Close();
                            border.setCenter(scre_1());
                        } catch (Exception ex) {

                        }
                    } else {
                        System.out.println("Loi");
                    }
                }
            });
            row10.getChildren().add(btnThem);
        } else {
        }
        row10.getChildren().add(btnHuy);
        scre_2.getChildren().addAll(row2, row4, row3, row6, row8, row10);
        scre_2.setPadding(new Insets(5, 30, 5, 30));
        return scre_2;
    }

    public boolean KiemTra() {
        boolean test = true;
        test = new Validate("họ và tên", txtNameSV.getText()).toString().equals("") ? false : true;
        test = new Validate("Bình luận", txtComment.getText()).toString().equals("") ? false : true;
        test = new Validate("Ngày tháng", txtDoB.getText()).toString().equals("") ? false : true;
        test = cbxCity.getSelectionModel().getSelectedIndex() == -1 ? false : true;

        return test;
    }

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        launch(args);
    }

}
